﻿-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[proc_Employee_Subsidy_GetList]
	(
	@startIndex int,
	@endindex int,
	@ename nvarchar(50),
	@DepId int,
	@companyid varchar(2000)
	
	)
AS
BEGIN

	declare @SQL nvarchar(4000),@bmbh varchar(30)
	select  @bmbh=bmbh from department where id=@DepId
	
	set @SQL='  eid<>0 '
	
	if @companyid<>'0'
	begin
		set @SQL=@SQL+' and companyid in ('+@companyid+') '
	end
	
	if(@DepId<>0)
	begin
		set @SQL=@SQL+' and departmentid in (select id from department where left(bmbh,'+Convert(varchar,len(@bmbh))+')='+@bmbh+'    ) '
	end
	
	if(@ename<>'')
	begin
		set @SQL=@SQL+' and  ename like ''%'+@ename+'%'' '
	end
	
	set @SQL=';WITH list as (select ROW_NUMBER() OVER (Order By ApplionMonth desc,ApplionTime DESC)AS Row,[EName],eid,id,gid,
	ApplionTime,[Companyid],[ApplionMoney],[ApplionMonth],flag,ProposerName,FirstAuditPersonName,FirstMemo,Reason ,Convert(varchar(10),FirstAuditDate,120) as FirstAuditDate,departmentid
					,(case companyname when '''' then (select companyname from company where company.id=companyid) else companyname end) as companyname
			  ,(case DepartmentName when '''' then (select DName from Department where id=departmentid) else DepartmentName end) as DepartmentName		from Employee_Subsidy where  '+@SQL+')
						
			  select *
			  ,(select count(1) from list ) as coun
			  
			   from list where row >='+Convert(varchar,@startIndex)+' and row <= '+Convert(varchar,@endindex)+' 
						
						
						'
	
	
	exec(@SQL)
	
END
